<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@page import="db.*,support.*,java.util.*,java.sql.*"%>
<%!@SuppressWarnings("unchecked")%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Confirmation</title>
</head>
<body>
	<%
		String sql = "INSERT INTO applicant "
				+ "(firstName, middleInit, lastName, residency, street, city, "
				+ "state, zipCode, countryCode, areaCode, telephone, citizenshipID, "
				+ "residenceID, specID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		String firstName = (String) session.getAttribute("firstname");
		String middleInitial = (String) session
				.getAttribute("middleinitial");
		String lastName = (String) session.getAttribute("lastname");
		String citizenship = (String) session.getAttribute("citizenship");
		Boolean isUS = (Boolean) session.getAttribute("isUS");
		String residency;
		if (!isUS) {
			residency = (String) session.getAttribute("residency");
		} else {
			session.setAttribute("residency", "US Citizen");
			residency = "US Citizen";
		}
		String residence = (String) session.getAttribute("residence");
		String address = (String) session.getAttribute("address");
		String city = (String) session.getAttribute("city");
		String state = (String) session.getAttribute("state");
		String countryCode = (String) session.getAttribute("countrycode");
		String zipCode = (String) session.getAttribute("zipcode");
		String areaCode = (String) session.getAttribute("areacode");
		String telNumber = (String) session.getAttribute("telnumber");
		String spec = (String) session.getAttribute("specialization");

		List<Map<String, String>> degrees = (LinkedList<Map<String, String>>) session
				.getAttribute("degrees");

		PreparedStatement pstmt = DBUtil.prepareStatement(sql);
		pstmt.setString(1, firstName);
		pstmt.setString(2, middleInitial);
		pstmt.setString(3, lastName);
		pstmt.setString(4, residency);
		pstmt.setString(5, address);
		pstmt.setString(6, city);
		pstmt.setString(7, state);
		pstmt.setString(8, zipCode);
		pstmt.setString(9, countryCode);
		pstmt.setString(10, areaCode);
		pstmt.setString(11, telNumber);
		ResultSet rs = DBUtil.createStatement().executeQuery(
				"SELECT id FROM Countries WHERE name = '" + citizenship
						+ "'");
		rs.next();
		//System.out.println(rs.getInt(1));
		pstmt.setInt(12, rs.getInt(1));
		rs = DBUtil.createStatement()
				.executeQuery(
						"SELECT id FROM Countries WHERE name = '"
								+ residence + "'");
		rs.next();
		pstmt.setInt(13, rs.getInt(1));
		rs = DBUtil.createStatement().executeQuery(
				"SELECT id FROM Specializations WHERE name = '" + spec
						+ "'");
		rs.next();
		pstmt.setInt(14, rs.getInt(1));
		DBUtil.beginTransaction();
		pstmt.executeUpdate();
		rs = pstmt.getGeneratedKeys();
		long applicantId = 0;
		if (rs.next()) {
			applicantId = Long.parseLong(rs.getString(1));
		}
		for (Map<String, String> d : degrees) {
			sql = "INSERT INTO Degree (studentID, gpa, month, year, "
					+ "title, universityID, disciplineID, otherDiscipline) VALUES "
					+ "(?, ?, ?, ?, ?, ?, ?, ?)";
			pstmt = DBUtil.prepareStatement(sql);
			pstmt.setLong(1, applicantId);
			pstmt.setDouble(2, Double.valueOf(d.get("gpa")));
			pstmt.setInt(3, Integer.valueOf(d.get("month")));
			pstmt.setInt(4, Integer.valueOf(d.get("year")));
			pstmt.setString(5, d.get("title"));
			rs = DBUtil.createStatement().executeQuery(
					"SELECT id FROM Universities WHERE name = '"
							+ d.get("university") + "' AND location = '"
							+ d.get("location") + "'");
			rs.next();
			System.out.println(d.get("university"));
			System.out.println(d.get("location"));
			pstmt.setInt(6, rs.getInt(1));
			System.out.println(rs.getInt(1));
			rs = DBUtil.createStatement().executeQuery(
					"SELECT id FROM Disciplines WHERE name = '"
							+ d.get("major") + "'");
			if (rs.next()) {
				pstmt.setInt(7, rs.getInt(1));
				pstmt.setString(8, "");
			} else {
				pstmt.setInt(7, 5);
				pstmt.setString(8, d.get("major"));
			}

			pstmt.executeUpdate();
		}
		DBUtil.commitTransction();
	%>
	Your application has been submitted.
	<br> Your application ID is
	<strong><%=applicantId%></strong>.
</body>
</html>